let express = require("express")();
let expressStatic = require("express-static");
let mysql = require("mysql");
//接受post请求的请求参数
let bodyParse = require("body-parser");
const port = 81;

//为body-parse配置
express.use(bodyParse.json());
express.use(bodyParse.urlencoded({ extended: false }));

//数据库配置
var sql = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '123456',
    database: 'students',
    timezone: "08:00",
});
sql.connect();

//路由请求

//登录接口
express.post("/login", (request, response) => {

    //接受get请求的请求参数
    // let q = request.query;

    //接受post请求的请求参数
    let q = request.body;
    sql.query(`select * from user where username="${q.username}" and password="${q.password}"`, (error, data) => {
        if (error) {
            console.log(error);
            response.send("0");
        } else {
            if (data.length === 0) {
                response.send("1");
            } else {
                response.send(JSON.stringify(data));
                console.log(`${q.username}用户登录成功`);
            }
        }
    })
})

//注册接口
express.post("/register", (request, response) => {
    // let q = request.query;
    let q = request.body;

    sql.query(`insert into user (username,password,usertype,realname) values ("${q.username}","${q.password}","${q.usertype}","${q.realname}")`, (error, data) => {
        if (error) {
            console.log(error);
            response.send("error");
        } else {
            response.send("success");
            console.log(`${q.realname}注册成功`);
        }
    })
})

//查看学生信息接口
express.get("/getStudents", (request, response) => {
    sql.query(`select * from student order by id`, (error, data) => {
        if (error) {
            console.log(error);
        } else {
            response.send(data);
        }
    })
})

//删除学生接口
express.get("/delete", (request, response) => {
    sql.query(`delete  from student where id=${request.query.id}`, (error, data) => {
        if (error) {
            console.log(error);
        } else {
            response.send("success");
        }
    })
})

//添加学生接口
express.get("/add", (request, response) => {
    let d = request.query;

    sql.query(`insert into student (name,age,sex,citys,joinDate) values ("${d.name}","${d.age}","${d.sex}","${d.city}","${d.joinDate}")`, (error, data) => {
        if (error) {
            console.log(error);
            response.send("error");
        } else {
            response.send("success");
            console.log(`新增学生${d.name}操作成功`);
        }
    })
})

//编辑学生接口
express.get("/edit", (request, response) => {
    let q = request.query;
    sql.query(`update student set name="${q.name}",age="${q.age}",sex="${q.sex}",citys="${q.city}",joinDate="${q.joinDate}" where id="${q.id}"`, (error, data) => {
        if (error) {
            console.log(error);
            response.send("error");
        } else {
            console.log(`查询${q.name}成功`);
            response.send("success");
        }
    })
})

//查看学生成绩接口
express.get("/score", (request, response) => {
    let q = request.query;
    sql.query(`update student set name="${q.name}",age="${q.age}",sex="${q.sex}",citys="${q.city}",joinDate="${q.joinDate}" where id="${q.id}"`, (error, data) => {
        if (error) {
            console.log(error);
            response.send("error");
        } else {
            console.log(`查询${q.name}成功`);
            response.send("success");
        }
    })
})

//静态请求
express.use(expressStatic(__dirname + '/static'));

console.log(`running at ${port}`);
express.listen(port);